﻿using CsvHelper;
using CsvHelper.Configuration;
using NetTaste;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Data;
using System.Dynamic;
using System.Globalization;
using System.Reflection;
using System.Text;
using System.Xml.Linq;

namespace Single.Npoi
{
    public static class NpoiHelper
    {
        #region Excel

        public static DataTable ExcelToDataTable(string filePath, string tableName, int sheetIndex = 0)
        {
            string extName = Path.GetExtension(filePath);

            DataTable dt = new DataTable();

            if (!string.IsNullOrEmpty(tableName))
            {
                dt.TableName = tableName;
            }

            if (extName.Equals(".xls") || extName.Equals(".xlsx"))
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook workbook = null;

                    switch (extName)
                    {
                        case ".xls": workbook = new HSSFWorkbook(file); break;
                        case ".xlsx": workbook = new XSSFWorkbook(file); break;
                        default: throw new Exception("未能识别的文件类型");
                    }

                    ISheet sheet = workbook.GetSheetAt(sheetIndex);

                    //列头
                    foreach (ICell item in sheet.GetRow(sheet.FirstRowNum).Cells)
                    {
                        var cellName = item.ToString();

                        if (!string.IsNullOrEmpty(cellName))
                        {
                            dt.Columns.Add(cellName);
                        }
                    }

                    //写入内容
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                    while (rows.MoveNext())
                    {
                        IRow row = (IRow)rows.Current;

                        if (row.RowNum == sheet.FirstRowNum)
                        {
                            continue;
                        }

                        DataRow dr = dt.NewRow();

                        foreach (ICell item in row.Cells)
                        {
                            if (item.ColumnIndex >= dt.Columns.Count) { continue; }

                            switch (item.CellType)
                            {
                                case CellType.Boolean:
                                    dr[item.ColumnIndex] = item.BooleanCellValue;
                                    break;
                                case CellType.Error:
                                    dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                    break;
                                case CellType.Formula:
                                    switch (item.CachedFormulaResultType)
                                    {
                                        case CellType.Boolean:
                                            dr[item.ColumnIndex] = item.BooleanCellValue;
                                            break;
                                        case CellType.Error:
                                            dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                            break;
                                        case CellType.Numeric:
                                            if (DateUtil.IsCellDateFormatted(item))
                                            {
                                                dr[item.ColumnIndex] = item.DateCellValue?.ToString("yyyy-MM-dd hh:MM:ss");
                                            }
                                            else { dr[item.ColumnIndex] = item.NumericCellValue; }
                                            break;
                                        case CellType.String:
                                            string str = item.StringCellValue;
                                            if (!string.IsNullOrEmpty(str))
                                            {
                                                dr[item.ColumnIndex] = str.ToString();
                                            }
                                            else
                                            {
                                                dr[item.ColumnIndex] = null;
                                            }
                                            break;
                                        case CellType.Unknown:
                                        case CellType.Blank:
                                        default:
                                            dr[item.ColumnIndex] = string.Empty;
                                            break;
                                    }
                                    break;
                                case CellType.Numeric:
                                    if (DateUtil.IsCellDateFormatted(item))
                                    {
                                        dr[item.ColumnIndex] = item.DateCellValue?.ToString("yyyy-MM-dd hh:MM:ss");
                                    }
                                    else { dr[item.ColumnIndex] = item.NumericCellValue; }
                                    break;
                                case CellType.String:
                                    string strValue = item.StringCellValue;
                                    if (!string.IsNullOrEmpty(strValue))
                                    {
                                        dr[item.ColumnIndex] = strValue.ToString();
                                    }
                                    else { dr[item.ColumnIndex] = null; }
                                    break;
                                case CellType.Unknown:
                                case CellType.Blank:
                                default: dr[item.ColumnIndex] = string.Empty; break;
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }

            return dt;
        }

        public static void DataTableToXls(DataTable dt, string sheetName, string filename)
        {
            //创建Excel
            IWorkbook book = new XSSFWorkbook();

            //创建工作薄
            ISheet sheet = book.CreateSheet(sheetName);

            //格式
            ICellStyle dateStyle = book.CreateCellStyle();
            IDataFormat format = book.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");

            //创建标题行
            IRow row = sheet.CreateRow(0);
            ICell cell = null;

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
            }

            //创建内容行
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                row = sheet.CreateRow(i + 1);

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);

                    string drValue = dt.Rows[i][j].ToString();

                    switch (dt.Rows[i][j].GetType().ToString())
                    {
                        case "System.String"://字符串类型
                            cell.SetCellValue(drValue);
                            break;
                        case "System.DateTime"://日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            cell.SetCellValue(dateV);
                            cell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            cell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            cell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            cell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理
                            cell.SetCellValue("");
                            break;
                        default:
                            cell.SetCellValue("");
                            break;
                    }
                }
            }

            using (var fs = new FileStream(filename, FileMode.Create, FileAccess.Write)) { book.Write(fs); }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="widths">列宽集合</param>
        /// <param name="sheetName"></param>
        /// <param name="filename"></param>
        /// <param name="isMerged">是否需要合并单元格</param>
        /// <exception cref="Exception"></exception>
        public static void DataTableToExcel(DataTable dt, List<int> widths, string sheetName, string filename, bool isMerged = false)
        {
            string extName = Path.GetExtension(filename);

            //创建Excel
            IWorkbook book = null;

            switch (extName)
            {
                case ".xls": book = new HSSFWorkbook(); break;
                case ".xlsx": book = new XSSFWorkbook(); break;
                default: throw new Exception("未能识别的文件类型");
            }

            //创建工作薄
            ISheet sheet = book.CreateSheet(sheetName);

            //格式
            ICellStyle dateStyle = book.CreateCellStyle();
            IDataFormat format = book.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
            dateStyle.Alignment = HorizontalAlignment.CenterSelection;
            dateStyle.VerticalAlignment = VerticalAlignment.Center;

            ICellStyle cellStyle = book.CreateCellStyle();
            cellStyle.WrapText = true;
            cellStyle.Alignment = HorizontalAlignment.CenterSelection;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;

            //创建标题行
            IRow row = sheet.CreateRow(0);

            ICell cell = null;

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                cell = row.CreateCell(i);

                cell.CellStyle = cellStyle;//格式化显示

                cell.SetCellValue(dt.Columns[i].ColumnName);
            }

            //设置列宽
            if (widths != null && widths.Count > 0)
            {
                for (int i = 0; i < widths.Count; i++)
                {
                    sheet.SetColumnWidth(i, (short)(35.7 * widths[i]));
                }
            }

            //创建内容行
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                row = sheet.CreateRow(i + 1);

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);

                    string drValue = dt.Rows[i][j].ToString();

                    switch (dt.Rows[i][j].GetType().ToString())
                    {
                        case "System.String"://字符串类型
                            if (drValue.Length > 32767)
                            {
                                drValue = drValue.Substring(0, 32767);
                            }
                            cell.SetCellValue(drValue);
                            cell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            cell.SetCellValue(dateV);
                            cell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            cell.SetCellValue(drValue);
                            cell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            cell.SetCellValue(intV);
                            cell.CellStyle = cellStyle;
                            break;
                        case "System.Int64":
                        case "System.Byte":
                            cell.SetCellValue(drValue);
                            cell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            cell.SetCellValue(doubV);
                            cell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理
                            cell.SetCellValue("");
                            cell.CellStyle = cellStyle;
                            break;
                        default:
                            cell.SetCellValue("");
                            cell.CellStyle = cellStyle;
                            break;
                    }
                }
            }

            //合并单元格
            if (isMerged)
            {
                SetMerged(sheet, dt, 0, 0, dt.Rows.Count);
            }

            using (FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write)) { book.Write(fs); }
        }

        private static void SetMerged(ISheet sheet, DataTable dt, int colIndex, int rowBegin, int rowEnd)
        {
            if (dt.Columns.Count > colIndex)
            {
                string tempValue = string.Empty;

                int tempCount = 0;

                for (int j = rowBegin; j <= rowEnd; j++)
                {
                    var rowValue = "${empty}";

                    if (!j.Equals(rowEnd))
                    {
                        rowValue = dt.Rows[j][colIndex].ToString();
                    }

                    if (!tempValue.Equals(rowValue))
                    {
                        if (tempCount > 1)
                        {
                            sheet.AddMergedRegion(new CellRangeAddress(j - tempCount + 1, j, colIndex, colIndex));
                            SetMerged(sheet, dt, colIndex + 1, j - tempCount, j);
                        }

                        tempValue = rowValue; tempCount = 0;
                    }

                    tempCount++;
                }
            }
        }

        public static void DataTableToExcel(DataTable[] dts, string[] sheetNames, string filename)
        {
            string extName = Path.GetExtension(filename);

            //创建Excel
            IWorkbook book = null;

            switch (extName)
            {
                case ".xls": book = new HSSFWorkbook(); break;
                case ".xlsx": book = new XSSFWorkbook(); break;
                default: throw new Exception("未能识别的文件类型");
            }

            for (int dtIndex = 0; dtIndex < dts.Length; dtIndex++)
            {
                //创建工作薄
                ISheet sheet = book.CreateSheet(sheetNames[dtIndex]);

                //格式
                ICellStyle dateStyle = book.CreateCellStyle();
                IDataFormat format = book.CreateDataFormat();
                dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
                dateStyle.Alignment = HorizontalAlignment.CenterSelection;
                dateStyle.VerticalAlignment = VerticalAlignment.Center;

                ICellStyle cellStyle = book.CreateCellStyle();
                cellStyle.WrapText = true;
                cellStyle.Alignment = HorizontalAlignment.CenterSelection;
                cellStyle.VerticalAlignment = VerticalAlignment.Center;

                //创建标题行
                IRow row = sheet.CreateRow(0);

                ICell cell = null;

                for (int i = 0; i < dts[dtIndex].Columns.Count; i++)
                {
                    cell = row.CreateCell(i);

                    cell.CellStyle = cellStyle;//格式化显示

                    cell.SetCellValue(dts[dtIndex].Columns[i].ColumnName);
                }

                //创建内容行
                for (int i = 0; i < dts[dtIndex].Rows.Count; i++)
                {
                    row = sheet.CreateRow(i + 1);

                    for (int j = 0; j < dts[dtIndex].Columns.Count; j++)
                    {
                        cell = row.CreateCell(j);

                        string drValue = dts[dtIndex].Rows[i][j].ToString();

                        switch (dts[dtIndex].Rows[i][j].GetType().ToString())
                        {
                            case "System.String"://字符串类型
                                if (drValue.Length > 32767)
                                {
                                    drValue = drValue.Substring(0, 32767);
                                }
                                cell.SetCellValue(drValue);
                                cell.CellStyle = cellStyle;
                                break;
                            case "System.DateTime"://日期类型
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                cell.SetCellValue(dateV);
                                cell.CellStyle = dateStyle;//格式化显示
                                break;
                            case "System.Boolean"://布尔型
                                cell.SetCellValue(drValue);
                                cell.CellStyle = cellStyle;
                                break;
                            case "System.Int16"://整型
                            case "System.Int32":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                cell.SetCellValue(intV);
                                cell.CellStyle = cellStyle;
                                break;
                            case "System.Int64":
                            case "System.Byte":
                                cell.SetCellValue(drValue);
                                cell.CellStyle = cellStyle;
                                break;
                            case "System.Decimal"://浮点型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                cell.SetCellValue(doubV);
                                cell.CellStyle = cellStyle;
                                break;
                            case "System.DBNull"://空值处理
                                cell.SetCellValue("");
                                cell.CellStyle = cellStyle;
                                break;
                            default:
                                cell.SetCellValue("");
                                cell.CellStyle = cellStyle;
                                break;
                        }
                    }
                }
            }

            using (FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write)) { book.Write(fs); }
        }

        public static void DataTableToExcel(List<DataTable> dtList, List<string> sheetNameList, string filename)
        {
            string extName = Path.GetExtension(filename);

            //创建Excel
            IWorkbook book = null;

            switch (extName)
            {
                case ".xls": book = new HSSFWorkbook(); break;
                case ".xlsx": book = new XSSFWorkbook(); break;
                default: throw new Exception("未能识别的文件类型");
            }

            for (int k = 0; k < dtList.Count; k++)
            {
                var dt = dtList[k];
                var sheetName = sheetNameList[k];

                //创建工作薄
                ISheet sheet = book.CreateSheet(sheetName);

                //格式
                ICellStyle dateStyle = book.CreateCellStyle();
                IDataFormat format = book.CreateDataFormat();
                dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
                dateStyle.Alignment = HorizontalAlignment.CenterSelection;
                dateStyle.VerticalAlignment = VerticalAlignment.Center;

                ICellStyle cellStyle = book.CreateCellStyle();
                cellStyle.WrapText = true;
                cellStyle.Alignment = HorizontalAlignment.CenterSelection;
                cellStyle.VerticalAlignment = VerticalAlignment.Center;

                //创建标题行
                IRow row = sheet.CreateRow(0);

                ICell cell = null;

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    cell = row.CreateCell(i);

                    cell.CellStyle = cellStyle;//格式化显示

                    cell.SetCellValue(dt.Columns[i].ColumnName);
                }

                //创建内容行
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    row = sheet.CreateRow(i + 1);

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        cell = row.CreateCell(j);

                        string drValue = dt.Rows[i][j].ToString();

                        switch (dt.Rows[i][j].GetType().ToString())
                        {
                            case "System.String"://字符串类型
                                if (drValue.Length > 32767)
                                {
                                    drValue = drValue.Substring(0, 32767);
                                }
                                cell.SetCellValue(drValue);
                                cell.CellStyle = cellStyle;
                                break;
                            case "System.DateTime"://日期类型
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                cell.SetCellValue(dateV);
                                cell.CellStyle = dateStyle;//格式化显示
                                break;
                            case "System.Boolean"://布尔型
                                cell.SetCellValue(drValue);
                                cell.CellStyle = cellStyle;
                                break;
                            case "System.Int16"://整型
                            case "System.Int32":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                cell.SetCellValue(intV);
                                cell.CellStyle = cellStyle;
                                break;
                            case "System.Int64":
                            case "System.Byte":
                                cell.SetCellValue(drValue);
                                cell.CellStyle = cellStyle;
                                break;
                            case "System.Decimal"://浮点型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                cell.SetCellValue(doubV);
                                cell.CellStyle = cellStyle;
                                break;
                            case "System.DBNull"://空值处理
                                cell.SetCellValue("");
                                cell.CellStyle = cellStyle;
                                break;
                            default:
                                cell.SetCellValue("");
                                cell.CellStyle = cellStyle;
                                break;
                        }
                    }

                }

                using (FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write)) { book.Write(fs); }
            }
        }

        /// <summary>
        /// 自动按照层级合并单元格，下级不会越过上级合并
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sheetName"></param>
        /// <param name="filename"></param>
        /// <param name="cols"></param>
        public static void MergedToXls(DataTable dt, string sheetName, string filename, params int[] widths)
        {
            //创建Excel
            IWorkbook book = new XSSFWorkbook();

            MergedToBook(book, dt, sheetName, widths);

            using (var fs = new FileStream(filename, FileMode.Create, FileAccess.Write))
            {
                book.Write(fs);
            }
        }

        private static void MergedToBook(IWorkbook book, DataTable dt, string sheetName, params int[] widths)
        {
            //创建工作薄
            ISheet sheet = book.CreateSheet(sheetName);

            //格式
            ICellStyle dateStyle = book.CreateCellStyle();
            IDataFormat format = book.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");

            ICellStyle cellStyle = book.CreateCellStyle();
            cellStyle.WrapText = true;
            cellStyle.Alignment = HorizontalAlignment.CenterSelection;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;

            //创建标题行
            IRow row = sheet.CreateRow(0);
            ICell cell = null;

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
            }

            for (int i = 0; i < widths.Length; i++)
            {
                sheet.SetColumnWidth(i, (short)(35.7 * widths[i]));
            }

            //创建内容行
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                row = sheet.CreateRow(i + 1);

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);

                    string drValue = dt.Rows[i][j].ToString();

                    switch (dt.Rows[i][j].GetType().ToString())
                    {
                        case "System.String"://字符串类型
                            cell.SetCellValue(drValue);
                            cell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            cell.SetCellValue(dateV);
                            cell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            cell.SetCellValue(drValue);
                            cell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            cell.SetCellValue(intV);
                            break;
                        case "System.Int64":
                        case "System.Byte":
                            cell.SetCellValue(drValue);
                            cell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            cell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理
                            cell.SetCellValue("");
                            break;
                        default:
                            cell.SetCellValue("");
                            break;
                    }
                }
            }

            SetMerged(sheet, dt, 0, 0, dt.Rows.Count);
        }

        #endregion

        #region CVS

        //自动识别Encoding
        private static Encoding GetEncoding(string fileName)
        {
            var bom = new byte[4];

            using (var file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                file.Read(bom, 0, 4);
            }

            if (bom[0] == 0x2b && bom[1] == 0x2f && bom[2] == 0x76) return Encoding.UTF7;
            if (bom[0] == 0xef && bom[1] == 0xbb && bom[2] == 0xbf) return Encoding.UTF8;
            if (bom[0] == 0xff && bom[1] == 0xfe) return Encoding.Unicode; //UTF-16LE
            if (bom[0] == 0xfe && bom[1] == 0xff) return Encoding.BigEndianUnicode; //UTF-16BE
            if (bom[0] == 0 && bom[1] == 0 && bom[2] == 0xfe && bom[3] == 0xff) return Encoding.UTF32;

            return Encoding.GetEncoding("GB2312");
        }

        public static DataTable CsvToDataTable(string fileName)
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                using (StreamReader sr = new StreamReader(fs, Encoding.Default))
                {
                    using (CsvReader cr = new CsvReader(sr, CultureInfo.CurrentCulture))
                    {
                        var records = cr.GetRecords<dynamic>().ToList();

                        DataTable dt = new DataTable();

                        if (records.Count > 0)
                        {
                            IDictionary<string, object> id = records[0];

                            foreach (var key in id.Keys)
                            {
                                dt.Columns.Add(key);
                            }

                            foreach (var item in records)
                            {
                                IDictionary<string, object> idic = item;

                                DataRow row = dt.NewRow();

                                foreach (var key in id.Keys)
                                {
                                    row[key] = idic[key];
                                }

                                dt.Rows.Add(row);
                            }
                        }

                        return dt;
                    }
                }
            }
        }

        public static List<T> CsvToListAutoColumn<T>(string fileName, int skip, string prefix, Action<T> action, Action<List<string>> setKeys) where T : class, new()
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                using (StreamReader sr = new StreamReader(fs, GetEncoding(fileName), true))
                {
                    // 跳过前skip行
                    for (int i = 0; i < skip; i++)
                    {
                        sr.ReadLine();
                    }

                    var config = new CsvConfiguration(CultureInfo.InvariantCulture)
                    {
                        BadDataFound = null     // 忽略错误数据
                    };

                    using (CsvReader cr = new CsvReader(sr, config))
                    {
                        var records = cr.GetRecords<dynamic>().ToList();

                        var list = new List<T>();

                        var t = typeof(T);

                        if (records.Count > 0)
                        {
                            IDictionary<string, object> id = records[skip];

                            setKeys(id.Keys.ToList());

                            foreach (var item in records)
                            {
                                IDictionary<string, object> idic = item;

                                var obj = new T();

                                int index = 0;

                                foreach (var key in id.Keys)
                                {
                                    var p = t.GetProperty($"{prefix}{index++}");

                                    if (idic[key] != null)
                                    {
                                        p?.SetValue(obj, idic[key].ToString());
                                    }
                                }

                                action(obj); list.Add(obj);
                            }
                        }

                        return list;
                    }
                }
            }
        }

        public static void DataTableToCsv(DataTable dt, string fileName)
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                using (StreamWriter sw = new StreamWriter(fs, Encoding.GetEncoding("gb2312")))
                {
                    using (CsvWriter cr = new CsvWriter(sw, CultureInfo.CurrentCulture))
                    {
                        var modelList = new List<dynamic>();

                        foreach (DataRow row in dt.Rows)
                        {
                            dynamic model = new ExpandoObject();

                            IDictionary<string, object> dict = model;

                            foreach (DataColumn column in dt.Columns)
                            {
                                dict[column.ColumnName] = $"'{row[column]}";
                            }
                            modelList.Add(model);
                        }

                        cr.WriteRecords(modelList);
                    }
                }
            }
        }

        #endregion

        public static void ExcelSetValues(string sourceFile, string newFile, List<NpoiSetValueSheet> sheets)
        {
            string ext = Path.GetExtension(sourceFile);

            if (ext.Equals(".xls") || ext.Equals(".xlsx"))
            {
                using (FileStream file = new FileStream(sourceFile, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook workbook = null;

                    switch (ext)
                    {
                        case ".xls": workbook = new HSSFWorkbook(file); break;
                        case ".xlsx": workbook = new XSSFWorkbook(file); break;
                        default: throw new Exception("未能识别的文件类型");
                    }

                    foreach (var sheet in sheets)
                    {
                        var iSheet = workbook.GetSheetAt(sheet.Index);

                        iSheet.ForceFormulaRecalculation = true;

                        var rowIndex = sheet.BeginRowIndex;

                        foreach (var row in sheet.Rows)
                        {
                            var iRow = iSheet.GetRow(rowIndex);

                            if (iRow == null) iRow = iSheet.CreateRow(rowIndex);

                            foreach (var cell in row.Cells)
                            {
                                var iCell = iRow.GetCell(cell.Index);

                                if (iCell == null) iCell = iRow.CreateCell(cell.Index);

                                switch (cell.Type)
                                {
                                    case "String":
                                        iCell.SetCellValue(cell.StringValue);
                                        break;
                                    case "Decimal":
                                        iCell.SetCellValue(cell.DoubleValue);
                                        break;
                                    case "Image":
                                        var bytes = System.IO.File.ReadAllBytes(cell.StringValue);
                                        var pIdx = workbook.AddPicture(bytes, PictureType.JPEG);
                                        var patriarch = iSheet.CreateDrawingPatriarch();
                                        var anchor = patriarch.CreateAnchor(0, 0, 0, 0, cell.Index, rowIndex, cell.Index + 1, rowIndex + 1);
                                        patriarch.CreatePicture(anchor, pIdx);
                                        break;
                                    default: break;
                                }
                            }

                            rowIndex++;
                        }

                        workbook.SetSheetName(sheet.Index, sheet.Title);
                    }

                    using (FileStream fs = new FileStream(newFile, FileMode.Create, FileAccess.Write)) { workbook.Write(fs); }
                }
            }
        }

        public static NpoiEntity<T> CreateEntity<T>(List<T> list) where T : class, new()
        {
            return new NpoiEntity<T>(list);
        }

        public static NpoiFile<T> CreateFile<T>(string path, int sheetIndex = 0) where T : class, new()
        {
            return new NpoiFile<T>(path, sheetIndex);
        }
    }
}